package cn.dao;

import cn.comon.DBConnection;
import cn.entity.PayMain;

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

public class PayMainDao implements AutoCloseable {

    private Connection conn;

    public PayMainDao() {
        conn = new DBConnection().getConn();
    }

    public List<Object[]> selectAll() {
        List<Object[]> data = new ArrayList<>();
        String sql = "SELECT * FROM paymain"; // 假设采购主表名为'PayMain'
        try (Statement sta = conn.createStatement();
             ResultSet rs = sta.executeQuery(sql)) {
            while (rs.next()) {
                Object[] rowData = new Object[6];
                rowData[0] = rs.getInt("Mid"); // 采购清单编号
                rowData[1] = rs.getString("Eid"); // 员工编号
                rowData[2] = rs.getInt("Mcount"); // 采购总数量
                rowData[3] = rs.getFloat("Paytotal"); // 采购总价
                rowData[4] = rs.getString("Ptime"); // 采购时间
                rowData[5] = rs.getString("Notes"); // 备注
                data.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return data;
    }

    public void addPayMain(List<PayMain> payMainsList) {
        String checkSql = "SELECT COUNT(*) FROM Employee WHERE Eid = ?";

        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
            conn.setAutoCommit(false);

            for (PayMain payMain : payMainsList) {
                // 检查Eid是否存在
                checkStmt.setString(1, payMain.getEid());
                ResultSet rs = checkStmt.executeQuery();
                rs.next();
                int count = rs.getInt(1);

                if (count == 0) { // Eid不存在于Employee表中
                    JOptionPane.showMessageDialog(null, "员工编号(" + payMain.getEid() + ")不存在，请检查后重试！", "错误", JOptionPane.ERROR_MESSAGE);
                    throw new SQLException("EID not found in Employee table.");
                }

                // 动态构建SQL插入语句
                String insertSql;
                if (payMain.getPtime() != null && !payMain.getPtime().isEmpty()) {
                    insertSql = "INSERT INTO PayMain (Mid, Eid, Ptime, Notes) VALUES (?, ?, ?, ?)";
                } else {
                    insertSql = "INSERT INTO PayMain (Mid, Eid, Ptime, Notes) VALUES (?, ?, current_date, ?)";
                }

                try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                    insertStmt.setInt(1, payMain.getMid());
                    insertStmt.setString(2, payMain.getEid());

                    if (payMain.getPtime() != null && !payMain.getPtime().isEmpty()) {
                        insertStmt.setString(3, payMain.getPtime());
                        insertStmt.setString(4, payMain.getNotes());
                    } else {
                        insertStmt.setString(3, payMain.getNotes());
                    }

                    insertStmt.executeUpdate();
                }
            }

            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            JOptionPane.showMessageDialog(null, "插入支付主表时发生错误: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
        }
    }


    public void deletePayMain(int mid) {
        String sql = "DELETE FROM paymain WHERE Mid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, mid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updatePayMain(PayMain updatedPayMain, String originalMid) throws SQLException {
        String sql = "UPDATE paymain SET Mid = ?, Eid = ?, Mcount = ?, Paytotal = ?, Ptime = ?, Notes = ? WHERE Mid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, updatedPayMain.getMid());
            ps.setString(2, updatedPayMain.getEid());
            ps.setInt(3, updatedPayMain.getMcount());
            ps.setFloat(4, updatedPayMain.getPaytotal());
            ps.setString(5, updatedPayMain.getPtime());
            ps.setString(6, updatedPayMain.getNotes());
            // 使用原始ID进行更新
            ps.setString(7, originalMid);

            ps.executeUpdate();
        } catch (SQLException e) {
            // 直接抛出SQLException，由调用者处理数据库更新失败的情况
            throw e;
        }
    }

    public List<PayMain> queryPayMain(PayMain criteria) {
        List<PayMain> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM PayMain WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (criteria.getMid() != 0) {
            sql.append(" AND Mid = ?");
            params.add(criteria.getMid());
        }
        if (criteria.getEid() != null && !criteria.getEid().isEmpty()) {
            sql.append(" AND Eid = ?");
            params.add(criteria.getEid());
        }
        if (criteria.isIncludeMcount()) {
            sql.append(" AND Mcount = ?");
            params.add(criteria.getMcount());
        }
        if (criteria.isIncludePaytotal()) {
            sql.append(" AND Paytotal = ?");
            params.add(criteria.getPaytotal());
        }
        if (criteria.getPtime() != null && !criteria.getPtime().isEmpty()) {
            sql.append(" AND Ptime = ?");
            params.add(criteria.getPtime());
        }
        if (criteria.getNotes() != null && !criteria.getNotes().isEmpty()) {
            sql.append(" AND Notes LIKE ?");
            params.add("%" + criteria.getNotes() + "%");
        }

        Logger logger = Logger.getLogger(getClass().getName());
        /*logger.info("Executing SQL: " + sql.toString() + " with parameters: " + params);
        在日志中记录查询的SQL语句和参数，以便调试和追踪问题，项目完成后注释掉*/
        try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof String) {
                    pstmt.setString(i + 1, (String) param);
                } else if (param instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) param);
                } else if (param instanceof Float) {
                    pstmt.setFloat(i + 1, (Float) param);
                } else if (param instanceof Double) {
                    pstmt.setDouble(i + 1, (Double) param);
                }
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    PayMain payMain = new PayMain();
                    payMain.setMid(rs.getInt("Mid"));
                    payMain.setEid(rs.getString("Eid"));
                    payMain.setMcount(rs.getInt("Mcount"));
                    payMain.setPaytotal(rs.getFloat("Paytotal"));
                    payMain.setPtime(rs.getString("Ptime"));
                    payMain.setNotes(rs.getString("Notes"));
                    result.add(payMain);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }



    @Override
    public void close() throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }
}